﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;

namespace BLL
{
    public class LopBUO:IBUO<tbl_Lop>
    {
        CSDLQLDiemHSC3DataContext context = new CSDLQLDiemHSC3DataContext();

        public bool Insert(tbl_Lop obj)
        {
            try
            {
                context.tbl_Lops.InsertOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool Delete(string id)
        {
            try
            {
                tbl_Lop obj = context.tbl_Lops.Where(p => p.id_Lop.Equals(id)).FirstOrDefault();
                context.tbl_Lops.DeleteOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Delete(tbl_Lop obj)
        {
            throw new NotImplementedException();
        }

        public List<tbl_Lop> GetAll()
        {
            throw new NotImplementedException();
        }

        public tbl_Lop GetSingle(string id)
        {
            return context.tbl_Lops.Where(p => p.id_Lop.Equals(id)).FirstOrDefault();
        }

        public bool Update(string id, tbl_Lop obj)
        {
            tbl_Lop lop = context.tbl_Lops.Where(p => p.id_Lop.Equals(id)).FirstOrDefault();
            lop.id_Khoi = obj.id_Khoi;
            lop.id_KhoaHoc = obj.id_KhoaHoc;
            lop.TenLop = obj.TenLop;

            context.SubmitChanges();
            return true;
        }

        public IQueryable HienThiLop()
        {
            var list = context.tbl_Khois.Join(
                    context.tbl_Lops,
                    a => a.id_Khoi,
                    b => b.id_Khoi,
                    (a, b) => new
                    {
                        id_Khoi = a.id_Khoi,
                        id_Lop = b.id_Lop,
                        id_KhoaHoc = b.id_KhoaHoc,
                        TenLop = b.TenLop,
                        TenKhoi = a.TenKhoi
                    }
                ).Join(
                    context.tbl_KhoaHocs,
                    c => c.id_KhoaHoc,
                    d => d.id_KhoaHoc,
                    (c, d) => new 
                    {
                        id_Khoi = c.id_Khoi,
                        id_Lop = c.id_Lop,
                        id_KhoaHoc = c.id_KhoaHoc,
                        TenLop = c.TenLop,
                        TenKhoi = c.TenKhoi,
                        KhoaHoc = d.NamBatDau + " - " + d.NamKetThuc
                    }
                ).OrderBy(l => l.TenLop);

            return list;
        }

        public IQueryable TimTheoTenLop(String tenlop)
        {
            var list = context.tbl_Khois.Join(
                    context.tbl_Lops,
                    a => a.id_Khoi,
                    b => b.id_Khoi,
                    (a, b) => new
                    {
                        id_Khoi = a.id_Khoi,
                        id_Lop = b.id_Lop,
                        id_KhoaHoc = b.id_KhoaHoc,
                        TenLop = b.TenLop,
                        TenKhoi = a.TenKhoi
                    }
                ).Join(
                    context.tbl_KhoaHocs,
                    c => c.id_KhoaHoc,
                    d => d.id_KhoaHoc,
                    (c, d) => new
                    {
                        id_Khoi = c.id_Khoi,
                        id_Lop = c.id_Lop,
                        id_KhoaHoc = c.id_KhoaHoc,
                        TenLop = c.TenLop,
                        TenKhoi = c.TenKhoi,
                        KhoaHoc = d.NamBatDau + " - " + d.NamKetThuc
                    }
                ).Where(p => p.TenLop.Contains(tenlop)).OrderBy(l => l.TenLop);

            return list;
        }

        public IQueryable TimTheoMaLop(String malop)
        {
            var list = context.tbl_Khois.Join(
                    context.tbl_Lops,
                    a => a.id_Khoi,
                    b => b.id_Khoi,
                    (a, b) => new
                    {
                        id_Khoi = a.id_Khoi,
                        id_Lop = b.id_Lop,
                        id_KhoaHoc = b.id_KhoaHoc,
                        TenLop = b.TenLop,
                        TenKhoi = a.TenKhoi
                    }
                ).Join(
                    context.tbl_KhoaHocs,
                    c => c.id_KhoaHoc,
                    d => d.id_KhoaHoc,
                    (c, d) => new
                    {
                        id_Khoi = c.id_Khoi,
                        id_Lop = c.id_Lop,
                        id_KhoaHoc = c.id_KhoaHoc,
                        TenLop = c.TenLop,
                        TenKhoi = c.TenKhoi,
                        KhoaHoc = d.NamBatDau + " - " + d.NamKetThuc
                    }
                ).Where(p => p.id_Lop.Contains(malop)).OrderBy(l => l.TenLop);

            return list;
        }

        public IQueryable getKhoaHoc()
        {
            return context.tbl_KhoaHocs.Select(n => new 
            {
                KhoaHoc = n.NamBatDau + " - " + n.NamKetThuc,
                id_KhoaHoc = n.id_KhoaHoc
            });
        }

        public IQueryable getKhoaHoc1(String id)
        {
            return context.tbl_KhoaHocs.Select(n => new
            {
                KhoaHoc = n.NamBatDau + " - " + n.NamKetThuc,
                id_KhoaHoc = n.id_KhoaHoc
            }).Where(p => p.id_KhoaHoc.Equals(id));
        }

        
    }
}
